package DAO;

import POJO.CauHinh;
import GUI.TOOL.IOUtils;
import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 *
 * @author khoi
 */
public class KetNoi {

	private static Connection conn = null;
	private static Driver driver;// = new org.gjt.mm.mysql.Driver(); 
	private static String url ="";// "jdbc:sqlserver://localhost:1433;databaseName=QLBH;user=nhom1;password=123";
	//= "jdbc:mysql://localhost:3306/qlda";
	private static Properties info;//= new Properties(); 
	private static Statement statement;
	private static CallableStatement statementProc;

//  Driver d = new com.microsoft.sqlserver.jdbc.SQLServerDriver();
//            DriverManager.registerDriver(d);
//            String url = "jdbc:sqlserver://localhost:1433;databaseName=QLBH;user=nhom1;password=123456";  
//            String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
//            Class.forName(driver).newInstance();
//            this.connection = DriverManager.getConnection(url);
	public KetNoi(int x) {
	}

	public static String docCauHinh() {
		String str = "";
		IOUtils ghiCauHinh = new IOUtils();
		CauHinh ch = new CauHinh();
		try {

			int t = ghiCauHinh.ReadFile("config.ini", ch);
			if (t == 1) {
				//this.setUrl("jdbc:sqlserver://localhost:1433;databaseName=QLBH;user=nhom1;password=123");
				str ="jdbc:sqlserver://"+ ch.getServer() +":"+ch.getPort() +";databaseName="+ ch.getDatabase() + ";user=" + ch.getUser() + ";password=" + ch.getPass();
			}
			//tmp.ReadFile("test.ini", str1, str2);
			//ghiCauHinh.Write2File("test.ini", ch);
			//System.out.println(str);

		} catch (IOException e) {
		}
		return str;
	}

	public KetNoi() throws SQLException {
		System.out.println("Mở kết nối.");

		try {
			this.driver = new com.microsoft.sqlserver.jdbc.SQLServerDriver();
			DriverManager.registerDriver(driver);
//			this.info = new Properties();
//			this.info.setProperty("characterEncoding", "utf8");
//			this.info.setProperty("user", "nhom1");
//			this.info.setProperty("password", "123");
			//this.setUrl("jdbc:sqlserver://localhost:1433;databaseName=QLBH;user=nhom1;password=123");
			this.conn = DriverManager.getConnection(url);
			System.out.println("Đã kết nối tới CSDL");

		} catch (SQLException e) {
			//Logger.getLogger(KetNoi.class.getName()).log(Level.SEVERE, null, e);
			throw e;

		}
	}

	public ResultSet docBang(String chuoiLenh) throws SQLException {
		ResultSet result = null;
		this.setStatement(this.getConn().createStatement());
		result = this.getStatement().executeQuery(chuoiLenh);
		return result;
	}

	/**
	 *
	 * @param tbName	ví dụ SINHVIEN, KHOA
	 * @param tbID	MaSV, MaKhoa
	 * @param soDong	số dòng của table
	 * @param trangSo	trang số mấy
	 * @return
	 * @throws SQLException
	 */
	public ResultSet docBangPhanTrang(String tbName, String tbID, int soDong, int trangSo) throws SQLException {
		ResultSet result = null;
		this.setStatement(this.getConn().createStatement());
		String sql = "with s as ( select ROW_NUMBER() OVER(ORDER By " + tbID + " desc) as row ,"
			 + tbName + ".*"
			 + " from " + tbName
			 + "  ) select * from s where row between "
			 + (trangSo * soDong + 1) + " and " + (trangSo * soDong + soDong)
			 + " order by " + tbID + " asc;";
		result = this.getStatement().executeQuery(sql);
		return result;
	}

	public int thucThi(String chuoiLenh) throws SQLException {
		int result = -1;
		this.setStatement(this.getConn().createStatement());
		result = this.getStatement().executeUpdate(chuoiLenh);
		return result;
	}

	/**
	 *
	 * @return kết nối
	 */
	public Connection thucThiProc() {
		return getConn();
	}

	/**
	 *
	 * @return truyền vao tham số cho hàm thucThiProc
	 */
	public CallableStatement thamSoProc() {
		return getStatementProc();
	}

	public boolean kiemTra(String chuoiLenh) throws SQLException {
		boolean result = false;
		this.setStatement(this.getConn().createStatement());
		result = this.getStatement().execute(chuoiLenh);
		return result;
	}

	public static void dongKetNoi() {
		try {
			getConn().close();
//    System.out.println("Disconnected from database");
			System.out.println();
			System.out.println("Đóng kết nối");

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public int thucThiProc(String tenProc, Object pra[]) throws SQLException {
		String str = "{ call " + tenProc + "(";
		if (pra != null) {
			for (int i = 0; i < pra.length; i++) {
				if (i != pra.length - 1) {
					str += "?,";
				} else {
					str += "?";
				}
			}
			str += ")}";

			this.setStatementProc(getConn().prepareCall(str));

			for (int i = 0; i < pra.length; i++) {
				this.getStatementProc().setString(i + 1, String.valueOf(pra[i].toString()));
			}
		} else {
			str += ")}";
			this.setStatementProc(getConn().prepareCall(str));
		}
		int rs = -1;
		rs = this.getStatementProc().executeUpdate();
		return rs;
	}

	public ResultSet docBang(String tenProc, Object pra[]) throws SQLException {
		String str = "{ call " + tenProc + "(";
		if (pra != null) {
			for (int i = 0; i < pra.length; i++) {
				if (i != pra.length - 1) {
					str += "?,";
				} else {
					str += "?";
				}
			}
			str += ")}";

			this.setStatementProc(getConn().prepareCall(str));

			for (int i = 0; i < pra.length; i++) {
				//if (pra[i] instanceof String) {
				this.getStatementProc().setString(i + 1, String.valueOf(pra[i].toString()));
				//}
				//if (pra[i] instanceof Integer) {
				//	this.getStatementProc().setInt(i + 1, Integer.valueOf(pra[i].toString()));
				//}
			}
		} else {
			str += ")}";
			this.setStatementProc(getConn().prepareCall(str));
		}
		this.getStatementProc().execute();
		ResultSet rs = null;
		rs = KetNoi.getStatementProc().executeQuery();
		return rs;
	}

	/**
	 * @return the conn
	 */
	public static Connection getConn() {
		return conn;
	}

	/**
	 * @return the statement
	 */
	public static Statement getStatement() {
		return statement;
	}

	/**
	 * @return the statementProc
	 */
	public static CallableStatement getStatementProc() {
		return statementProc;
	}

	/**
	 * @param aConn the conn to set
	 */
	public static void setConn(Connection aConn) {
		conn = aConn;
	}

	/**
	 * @return the driver
	 */
	public static Driver getDriver() {
		return driver;
	}

	/**
	 * @param aDriver the driver to set
	 */
	public static void setDriver(Driver aDriver) {
		driver = aDriver;
	}

	/**
	 * @return the url
	 */
	public static String getUrl() {
		return url;
	}

	/**
	 * @param aUrl the url to set
	 */
	public static void setUrl(String aUrl) {
		url = aUrl;
	}

	/**
	 * @return the info
	 */
	public static Properties getInfo() {
		return info;
	}

	/**
	 * @param aInfo the info to set
	 */
	public static void setInfo(Properties aInfo) {
		info = aInfo;
	}

	/**
	 * @param aStatement the statement to set
	 */
	public static void setStatement(Statement aStatement) {
		statement = aStatement;
	}

	/**
	 * @param aStatementProc the statementProc to set
	 */
	public static void setStatementProc(CallableStatement aStatementProc) {
		statementProc = aStatementProc;
	}
}